"""
数据库操作之增删改查
"""
from pymysql import Connection

from day13.Hero import Hero


class Operation:
    def __init__(self):
        """连接数据库"""
        """因为连接对象和游标对象只需要创建一次，所以放在构造函数中"""
        self.conn = Connection(
            host="localhost",
            port=3306,
            user="root",
            password="root",
            database="db03",
            charset="utf8",
            autocommit=True  # 设置自动提交
        )
        self.cursor = self.conn.cursor()

    def insert(self,hero):
        """增"""
        # sql = f"INSERT INTO hero(h_name,age,gender,address) VALUES('{hero.name}',{hero.age},'{hero.gender}','{hero.address}')"
        sql = f"INSERT INTO hero(h_name,age,gender,address) VALUES(%(name)s,%(age)s,%(gender)s,%(address)s)"
        self.cursor.execute(sql,hero.__dict__)
        # self.conn.commit()
        print("数据添加完成")


    def delete(self,id):
        """删"""
        # sql = f"DELETE FROM hero WHERE id=%s"
        # self.cursor.execute(sql,id)
        sql = f"DELETE FROM hero WHERE id={id}"
        self.cursor.execute(sql)
        print(f"删除{id}成功")


    def update(self,hero):
        """改"""
        sql = "UPDATE hero SET h_name=%(name)s,age=%(age)s,gender=%(gender)s,address=%(address)s WHERE id = %(id)s"
        self.cursor.execute(sql,hero.__dict__)
        print("修改成功")

    def selectAll(self):
        """查"""
        sql = "SELECT * FROM hero"
        self.cursor.execute(sql)
        query = self.cursor.fetchall()
        for row in query:
            print(row)

    def selectById(self,id):
        """根据Id查询"""
        sql = f"SELECT * FROM hero WHERE id = {id}"
        self.cursor.execute(sql)
        hero = self.cursor.fetchone()
        print(hero)



    def closeDB(self):
        """释放资源"""
        if self.cursor:
            self.cursor.close()

        if self.conn:
            self.conn.close()



if __name__ == '__main__':
    o = Operation()
    # 增
    # hero = Hero(null,"公孙胜",30,"男","天津市宝坻区")
    # o.insert(hero)

    # 删除
    # o.delete(4)
    # hero = Hero(8,"入云龙", 31, "男", "天津市宝坻区")
    # o.update(hero)
    # o.selectAll()
    o.selectById(10)
    o.closeDB()
